Skip to main content

Aggregates and Functions

Stateless Functions​

Stateless Functions work with fields of one specific class/field.

Functions for Numerics​

ExpressionExplanation
max(x,y)Maximum
min(x,y)Minimum
abs(x)Absolute value
sqrt(x)Square root
log(x)Natural logarithm (base e).
exp(x)Euler's number e raised to the power of a value
floor(x)Largest value that is less than or equal to the argument and is equal to a mathematical integer.
ceil(x)Smallest value that is greater than or equal to the argument and is equal to a mathematical integer.
#Shell format

==> select max(1,23)

>_,TIMESTAMP,SYMBOL,TYPE,"MAX (1, 23)"
0,_,,CUSTOM,23

==> select abs(-10)

>_,TIMESTAMP,SYMBOL,TYPE,ABS (-10)
0,_,,CUSTOM,10

==> select float32(10.1123456789)

>_,TIMESTAMP,SYMBOL,TYPE,FLOAT32 (101123456789E-10)
0,_,,CUSTOM,10.112346

Functions for VARCHAR​

Functions for VARCHAR datatype.

ExpressionExplanation
length(s)string length
uppercase(s)uppercase string
lowercase(s)lowercase string
reverse(s)reverse string
indexof(x, y)find y in x, return index
substr(x, start, end)substring
toTimestamp(string, format)parse string into timestamp(ms)
toTimestampNs(string, format)parse string into timestamp(ns)
toTimestamp(string)parse string into timestamp(ms) (use default Timestamp format*)
toTimestampNs(string)parse string into timestamp(ns) (use default Timestamp format*)
#Shell format

==> select length('hello')

>_,TIMESTAMP,SYMBOL,TYPE,LENGTH (hello)
0,_,,CUSTOM,5

==> select uppercase('hello')

>_,TIMESTAMP,SYMBOL,TYPE,UPPERCASE (hello)
0,_,,CUSTOM,HELLO 

==> select lowercase('HeLlo')

>_,TIMESTAMP,SYMBOL,TYPE,LOWERCASE (HeLlo)
0,_,,CUSTOM,hello 

==> select reversed('hello')

>_,TIMESTAMP,SYMBOL,TYPE,REVERSED (hello)
0,_,,CUSTOM,olleh

==> select indexof('h', 'hello')

>_,TIMESTAMP,SYMBOL,TYPE,"INDEXOF (h, hello)"
0,_,,CUSTOM,-1 

==> select substr('Hello, World!', 7, 14)

>_,TIMESTAMP,SYMBOL,TYPE,"SUBSTR (Hello, World!, 7, 14)"
0,_,,CUSTOM,World!
SELECT length(entry.exchangeid) AS length
FROM bittrex
ARRAY JOIN entries IN entry
info

toTimestamp and toTimestampNs are available since 5.6.111+.

info

* Default format for toTimestamp (and toTimestampNs):

[yyyyMMdd][yyyy-MM-dd][yyyy-DDD]['T'[HHmmss][HHmm][HH:mm:ss][HH:mm][.SSSSSSSSS][.SSSSSS][.SSS][.SS][.S]][OOOO][O][z][XXXXX][XXXX]['['VV']']
-- Parse timestamp with default format:
select toTimestamp('2016-10-27T16:36:08.993+02:00:00[Europe/Paris]')
select toTimestamp('2016-10-27T16:36:08.993+02:00[Europe/Paris]')
select toTimestamp('2016-10-27T16:36:08.993+020000[Europe/Paris]')
select toTimestamp('2016-10-27T16:36:08.993+0200[Europe/Paris]')
select toTimestamp('2016-10-27T16:36:08.993GMT+1[Europe/Paris]')
select toTimestamp('2016-10-27T16:36:08.993PST[Europe/Paris]')
select toTimestamp('2016-10-27T16:36:08.993[Europe/Paris]')
select toTimestamp('2016-10-27T16:36:08.993+02:00:00')
select toTimestamp('2016-10-27T16:36:08.993Z')
select toTimestamp('2016-10-27T16:36:08.993')
select toTimestampNs('2016-10-27T16:36:08.000993')
select toTimestampNs('2016-10-27T16:36:08.000000993')
select toTimestamp('2016-10-27T16:36:08+0200')
select toTimestamp('2016-10-27T16:36:08')
select toTimestamp('2016-10-01T16:36:08Z')
select toTimestamp('2016-10-27')
select toTimestamp('20161223T163608')
select toTimestamp('20161223T1636')

-- Parse timestamp with specified format:
SELECT toTimestamp('2022-08-27 22:32:02.123', 'yyyy-MM-dd HH:mm:ss.SSS')
SELECT toTimestampNs('2022-08-27 22:32:02.123456789', 'yyyy-MM-dd HH:mm:ss.SSSSSSSSS')
SELECT toTimestamp('2024-08 27 22:32 - 02 123 Europe/Moscow', 'yyyy-MM dd HH:mm - ss SSS v')

Functions for Arrays​

ExpressionExplanation
empty(arr)is array empty
notempty(arr)is array not empty
size(arr)array size
max(arr)array maximum
min(arr)array minimum
mean(arr)array mean
sum(arr)array sum
enumerate(arr)array indices
sort(arr)array sort
indexof(arr, el)find index of element
any(arr)if any element is true
all(arr)if all elements are true
Examples with functions for arrays
SELECT
sum(entries.size) AS SUM
FROM bittrex

SELECT
avg(entries.price) AS AVG
FROM bittrex

SELECT sort(entries.price) FROM bittrex

SELECT size(entries.price) FROM bittrex

SELECT 
ANY(entries.price > 200)
FROM bittrex

SELECT * FROM bitfinex 
WHERE notEmpty(entries[THIS IS deltix.timebase.api.messages.universal.TradeEntry])

Internal Functions​

ExpressionExplanation
now()returns the current timestamp as a constant at the moment of the query's execution
currentTimeMs()continually returns the current time in milliseconds as the query is being executed, providing a dynamic time value throughout the execution process
streams()returns an array of streams with full schema information
typeOf(expr)returns actual type of expression or object
symbols(stream key)returns an array of symbols in stream
spaces(stream key)returns an array of spaces in stream
stateless_functions()returns an array of stateless functions supported by QQL
stateful_functions()returns an array of stateful functions supported by QQL
Examples with internal functions
-- Select all streams
SELECT s.key
ARRAY JOIN streams() AS s

-- Select all symbols from securities stream
SELECT s
ARRAY JOIN symbols('securities') AS s

-- Select all stateless functions with arguments and types
SELECT f.id, f.arguments.name, f.arguments.dataType.baseName
ARRAY JOIN STATELESS_FUNCTIONS() as f

-- Count entries grouped by type
select typeof(e), count{}() from KRAKEN 
array join this.entries as e
group by typeof(e)

Stateful Functions​

Keywords​

Time template:

SELECT [RUNNING] 
function{arg1: value1, arg2: value2}(arg1, arg2)
FROM stream [TRIGGER/RESET] OVER [EVERY] TIME(interval[, offset])

Count template:

SELECT [RUNNING] 
function{arg1: value1, arg2: value2}(arg1, arg2)
FROM stream [TRIGGER] OVER COUNT(100)
  • RUNNING - the result is returned for every input message. For example we compute running max, it means, that for each message we receive max for this message and all previous messages in a group.
  • OVER [EVERY][OPEN|CLOSE] TIME(5m) - computes functions separately for each (5 minutes) interval and delivers updates at the end of every (5 minutes) interval. If we add EVERY, we expect empty result for empty 5m intervals when no messages were published.
  • OVER [EVERY][OPEN|CLOSE] TIME(week|month|quarter|year) - computes functions separately for each calendar interval (week, month, quarter or year) and delivers updates at the end of every interval.
  • OVER [OPEN|CLOSE] TIME(1d, 10h) - computes functions separately for each 1 day interval with 10 hours offset and delivers updates daily at 10:00.
  • TRIGGER OVER [EVERY][OPEN|CLOSE] TIME(5m) - receive function results every 5 minutes and function is computed over the entire stream. If we add EVERY, we expect update for empty 5m intervals when no messages were published.
  • OVER COUNT(100) - compute function separately for each 100 messages group and receive update every 100 messages.
  • TRIGGER OVER COUNT(100) - compute function over the entire stream, but receive updates every 100 messages.
  • RESET - is used to reset function based on provided conditions. For example, reset running calculation for each time period instead of carrying on with the cumulative counting.

OPEN TIME aggregates over [start, end) intervals (start inclusive, end exclusive), using the interval’s open (start) timestamp as the output timestamp. This mode cannot be used with running mode.

CLOSE TIME aggregates over (start, end] intervals (start exclusive, end inclusive), using the interval’s close (end) timestamp as the output timestamp. This mode is the default aggregation mode and does not need to be explicitly specified in the query.

caution

OVER TIME produces interval message only at a time when a new message arrives from a source. For example, for OVER TIME (1m) if message for new interval arrives at 10:51:08.458, QQL will produce interval message with timestamp 10:51:00.000 with 8.458 seconds delay.

caution

Offset for OVER TIME(interval[, offset]) is available since 5.6.78.

info

The OPEN and CLOSE keywords are available from version 5.6.152.

info

Calendar intervals (like week, month, quarter, year) are available from version 5.6.154.

Functions Syntax​

function{initArg1: value1, initArg2: value2, ...}(argValue1, argValue2)

OR

function{value1, value2, ...}(argValue1, argValue2, ...)

Init arguments could be passed to function like named args and also like position arguments in {...} braces. Init arguments are constant, so you cannot pass here selectors or anything similar. Arguments are passed to function in (...) parentheses as positional arguments.

Examples​

messagemax(field)
indextimestampfield
12021-03-19T08:07:31.373623
22021-03-19T08:07:32.48736
32021-03-19T08:07:32.683237
42021-03-19T08:07:32.9853467
52021-03-19T08:07:33.18597
62021-03-19T08:07:33.2002374
72021-03-19T08:07:33.37539
82021-03-19T08:07:33.687-49
92021-03-19T08:07:34.100347
102021-03-19T08:07:36.101-347
112021-03-19T08:07:37.1024737
122021-03-19T08:07:38.103499
132021-03-19T08:07:41.1047
indextimestampmax
1
2
3
4
5
6
7
8
9
10
11
12
132021-03-19T08:07:41.1044737
messagemax(field) over time(1s)
indextimestampfield
12021-03-19T08:07:31.373623
22021-03-19T08:07:32.48736
32021-03-19T08:07:32.683237
42021-03-19T08:07:32.9853467
52021-03-19T08:07:33.18597
62021-03-19T08:07:33.2002374
72021-03-19T08:07:33.37539
82021-03-19T08:07:33.687-49
92021-03-19T08:07:34.100347
102021-03-19T08:07:36.101-347
112021-03-19T08:07:37.1024737
122021-03-19T08:07:38.103499
132021-03-19T08:07:41.1047
timestampfield
2021-03-19T08:07:32623
2021-03-19T08:07:333467
2021-03-19T08:07:342374
2021-03-19T08:07:35347
2021-03-19T08:07:37-347
2021-03-19T08:07:384737
2021-03-19T08:07:39499
2021-03-19T08:07:427
messagemax(field) trigger over count(5)
indextimestampfield
12021-03-19T08:07:31.373623
22021-03-19T08:07:32.48736
32021-03-19T08:07:32.683237
42021-03-19T08:07:32.9853467
52021-03-19T08:07:33.18597
62021-03-19T08:07:33.2002374
72021-03-19T08:07:33.37539
82021-03-19T08:07:33.687-49
92021-03-19T08:07:34.100347
102021-03-19T08:07:36.101-347
112021-03-19T08:07:37.1024737
122021-03-19T08:07:38.103499
132021-03-19T08:07:41.1047
indextimestampfield
1
2
3
4
52021-03-19T08:07:33.1853467
6
7
8
9
102021-03-19T08:07:36.1013467
11
12
132021-03-19T08:07:41.1044737
-- max price for the entire stream
SELECT 
max{} (max(entries.price)) AS MAX 
FROM bittrex

-- max for every hour time period
SELECT 
max{} (max(entries.price)) AS MAX 
FROM bittrex
OVER TIME (1h)

-- max from all prior messages for each message
SELECT
RUNNING 
max{} (max(entries.price)) AS MAX 
FROM bittrex

-- current and total MAX for every message for the time interval
SELECT RUNNING
max{} (max(entries.price)) AS MAX 
FROM bittrex
OVER TIME (10m)

-- max value from prior 10 messages for each interval
SELECT
max{} (max(entries.price)) AS MAX 
FROM binance
OVER COUNT (10)

-- every 10 messages returns max from all prior messages
SELECT
max{} (max(entries.price)) AS MAX 
FROM binance
TRIGGER OVER COUNT (10)

-- every 10 min returns max from all prior messages
SELECT 
max{} (max(entries.price)) AS MAX 
FROM bittrex
TRIGGER OVER TIME (10m)

-- returns max from all prior messages for every message, resets every 30 min but does not return a snapshot
SELECT RUNNING
max{} (max(entries.price)) AS MAX 
FROM bittrex
RESET OVER TIME (30m)

-- incremental update for every message and a snapshot for every 10 messages
SELECT RUNNING
max{} (max(entries.price)) AS MAX 
FROM binance
OVER COUNT (10)

-- select BID and ASK price FROM L2EntryNew
-- cast entries array to L2EntryNew type
-- return max for every selection every 1 min
-- filter by packageType and symbol
WITH 
  (entries AS array(deltix.timebase.api.messages.universal.L2EntryNew)) AS 'entries',
  entries[side == BID].price AS 'bidPrices',
  entries[side == ASK].price AS 'askPrices'
SELECT
  max{}(max(askPrices)) AS 'highAsk',
  max{}(max(bidPrices)) AS 'highBid',
  min{}(min(askPrices)) AS 'lowAsk',
  min{}(min(bidPrices)) AS 'lowBid'
FROM binance
OVER TIME(1m)
WHERE packageType == PERIODICAL_SNAPSHOT
AND symbol == 'BTC/USDT'

-- one-minute bars based on trades 
WITH 
  entries[THIS IS TradeEntry] AS 'entries'
SELECT
  sum{}(sum(entries.size)) AS 'volume',
  first{}(entries[0].price) AS 'open',
  last{}(entries[-1].price) AS 'close',
  max{}(max(entries.price)) AS 'high',
  min{}(min(entries.price)) AS 'low'
FROM binance
OVER TIME(1m)
WHERE symbol == 'BTC/USDT' AND size(entries) > 0

-- one-month bars based on trades for open included inervals
WITH
  entries[THIS IS TradeEntry] AS 'entries'
SELECT
  sum{}(sum(entries.size)) AS 'volume',
  first{}(entries[0].price) AS 'open',
  last{}(entries[-1].price) AS 'close',
  max{}(max(entries.price)) AS 'high',
  min{}(min(entries.price)) AS 'low'
FROM binance
OVER OPEN TIME(month)
WHERE symbol == 'BTC/USDT' AND size(entries) > 0

-- different price indicators 
WITH 
  entries[THIS IS TradeEntry].price AS 'prices'
SELECT
  sma{timePeriod: 1h}(price) AS 'sma',
  (bollinger{timeWindow: 1h}(price) AS 'bollinger').*,
  cma{}(price) AS 'cma'
FROM bitfinex
ARRAY JOIN prices AS 'price'
OVER TIME(10m)
WHERE symbol == 'BTC/USDT' AND size(entries) > 0

-- simple moving average for Prices for 1 min time period returned every 1 hour for each symbol
SELECT
  sma{timePeriod: 1m}(max(entries.price))
FROM bittrex
TRIGGER OVER TIME(1h)
GROUP BY symbol

-- Volume-Weighted Average Price (VWAP)
SELECT sum{}(trade.price * trade.size) / sum{}(trade.size)
FROM bitfinex
ARRAY JOIN entries[this is TradeEntry] as trade
OVER TIME (1m)
WHERE symbol == 'BTCUSD'

-- VWAP cumulative
SELECT sum{}(trade.price * trade.size) / sum{}(trade.size)
FROM bitfinex
ARRAY JOIN entries[this is TradeEntry] as trade
TRIGGER OVER TIME (1m)
WHERE symbol == 'BTCUSD'

-- counts the number of unique FX instruments in the securities stream
SELECT size(collect_unique{}(symbol)) FROM "securities"
where type == "deltix.timebase.api.messages.InstrumentType":FX

-- builds L2 order book with 10 levels size for bittrex BTC/USDT and returns snapshots every 10 seconds
SELECT orderbook{maxDepth: 10}(this.packageType, this.entries)
FROM bittrex
OVER TIME (10s)
WHERE symbol == 'BTC/USDT'

-- builds L2 order book and returns snapshots in Universal format
WITH
  orderbook{maxDepth: 10}(this.packageType, this.entries) as book
SELECT
  book as entries, 
  PERIODICAL_SNAPSHOT as packageType
TYPE "deltix.timebase.api.messages.universal.PackageHeader"
FROM BITFINEX
OVER TIME (10s)
WHERE symbol == 'BTC/USDT'

-- builds L3 order book and returns snapshots in Universal format (L3 is supported since 5.6.95+) for each symbol
WITH
  orderbook{maxDepth: 10, model: 'L3'}(this.packageType, this.entries) as book
SELECT
  book as entries, 
  PERIODICAL_SNAPSHOT as packageType
TYPE "deltix.timebase.api.messages.universal.PackageHeader"
FROM MBO
OVER TIME (10s)
WHERE size(book) > 0
GROUP BY symbol

-- calculate sum of trade quantities for each day, given that the trading day starts at 15:00 UTC
SELECT
  SUM{}(tradeQuantity * -1 if orderEvent:Side == SELL else tradeQuantity) 
FROM "ember-messages"
OVER Time(1d, 15h)
WHERE orderStatus IN (COMPLETELY_FILLED, PARTIALLY_FILLED)
 AND symbol IN ('BTCUSD')
GROUP BY symbol

-- returns maximum price and timestamp where the last maximum price was found
SELECT max{}(price) as `max_price`, timeOfMax{}(price)[-1] as `max_price_time` FROM "trades"

List of Functions​

IDInit argsArgsReturnsDescription
COUNTINT64counts messages
MAXBOOLEAN?, INT8?, INT16?, INT32?, INT64?, FLOAT32?, FLOAT64?, DECIMAL64?, TIMESTAMP?, TIMEOFDAY?, VARCHAR?BOOLEAN?, INT8?, INT16?, INT32?, INT64?, FLOAT32?, FLOAT64?, DECIMAL64?, TIMESTAMP?, TIMEOFDAY?, VARCHAR?computes maximum value
MAXtimePeriod: INT64FLOAT64?FLOAT64?computes maximum over time window with given timePeriod
MAXperiod: INT64FLOAT64?FLOAT64?computes maximum over count window with given period
MINBOOLEAN?, INT8?, INT16?, INT32?, INT64?, FLOAT32?, FLOAT64?, DECIMAL64?, TIMESTAMP?, TIMEOFDAY?, VARCHAR?BOOLEAN?, INT8?, INT16?, INT32?, INT64?, FLOAT32?, FLOAT64?, DECIMAL64?, TIMESTAMP?, TIMEOFDAY?, VARCHAR?computes minimum value
MINtimePeriod: INT64FLOAT64?FLOAT64?computes minimum over time window with given timePeriod
MINperiod: INT64FLOAT64?FLOAT64?computes minimum over count window with given period
SUMINT8?, INT16?, INT32?, INT64?, DECIMAL64?DECIMAL64?computes sum of given values
SUMFLOAT32?, FLOAT64?FLOAT32?, FLOAT64?computes sum of given values
SUMtimePeriod: INT64DECIMAL64?DECIMAL64?computes sum of values in time window with given time period
SUMperiod: INT32DECIMAL64?DECIMAL64?computes sum of values in count window with given period
AVGINT8?, INT16?, INT32?, INT64?, DECIMAL64?DECIMAL64?computes avg of given values
AVGFLOAT32?, FLOAT64?FLOAT32?, FLOAT64?computes avg of given values
SMAtimePeriod: INT64FLOAT64?FLOAT64?computes moving average over time window with given timePeriod
SMAperiod: INT64FLOAT64?FLOAT64?computes moving average over count window with given period
CMAFLOAT64?FLOAT64?computes cumulative moving average
EMAperiod: INT32FLOAT64?FLOAT64?computes exponential moving average with given period
EMAfactor: FLOAT64FLOAT64?FLOAT64?computes exponential moving average with given factor
ADXRperiod: INT64open, high, low, close, volume (FLOAT64)ADXRMessagecomputes Average Directional Movement Rating indicator (read more)
ATRperiod: INT64open, high, low, close, volume (FLOAT64)FLOAT64computes Average True Range (read more)
BOLLINGERpointWindow: INT64 or timeWindow: INT64, factor: FLOAT64FLOAT64BollingerMessagecomputes Bollinger Bands (read more)
KAMAperiod: INT64FLOAT64FLOAT64computes Kaufman's Adaptive Moving Average (read more)
LSMApointWindow: INT64 or timeWindow: INT64, useDateTime: BOOLEANFLOAT64LSMAMessagecomputes Least Squares Moving Average (read more)
MMAperiod: INT64FLOAT64FLOAT64computes Modified Moving Average (read more)
COLLECT_UNIQUEVARCHAR?ARRAYS OF VARCHARScollects and returns an array of unique strings
lastNotNullBOOLEAN?, INT8?, INT16?, INT32?, INT64?, FLOAT32?, FLOAT64?, DECIMAL64?, TIMESTAMP?, TIMEOFDAY?, VARCHAR?, ARRAY?BOOLEAN?, INT8?, INT16?, INT32?, INT64?, FLOAT32?, FLOAT64?, DECIMAL64?, TIMESTAMP?, TIMEOFDAY?, VARCHAR?, ARRAY?fills null gaps with previous not null value
windowperiod: INT64 or timePeriod: INT64FLOAT64?ARRAY OF FLOAT64?builds fixed size (if period is set) or time (if timePeriod is set) window and returns it as an array
orderBookmaxDepth: INT32, model: VARCHAR ('L1', 'L2' (default), 'L3')ENUM(PackageType), ARRAY of OBJECT(BaseEntry)ARRAY of OBJECT(BaseEntry)builds order book
statWindowperiod: INT64 or timePeriod: INT64FLOAT64?OBJECT(StatWindowMessage), where StatWindowMessage contains: sum, count, sumOfSquares, sumOfAbs, geometricMean, harmonicMean, firstRawMoment, secondRawMoment, thirdRawMoment, forthRawMoment, variance, standardDeviation, median, min, maxcalculates statistics over fixed size (if period is set) window or time window (if timePeriod is set).
timeOfMin*INT8?, INT16?, INT32?, INT64?, FLOAT32?, FLOAT64?, DECIMAL64?ARRAY(TIMESTAMP)returns list of timestamps for each minimum value.
timeOfMax*INT8?, INT16?, INT32?, INT64?, FLOAT32?, FLOAT64?, DECIMAL64?ARRAY(TIMESTAMP)returns list of timestamps for each maximum value.
histogramq: ARRAY of FLOAT (quantiles to calculate), significantDigits: INT32, max: INT32, min: INT32, maxToMinRatio: FLOAT64FLOAT64 or INT64ARRAY of OBJECT(FLOAT64 or INT64)Computes quantiles over the distribution of a numeric expression.

* maximum size of the array can be returned by the function is restricted by 1'000'000 elements, To control the value use -DTimeBase.qql.functions.maxArraySize=2000000 system property.

OrderBook Price/Volume Query functions​

info

Order Book Price/Volume Query functions are available starting with TimeBase 5.6.160.

askPriceForVolume() / bidPriceForVolume()​

Returns the price required to execute a given volume on the ask/bid side of the order book. If there is less volume on the market than chosen, the function returns NaN. Note that in case of negative volume provided, the function not necessarily returns NaN. It returns the first level price if there is at least one level in the book.

WITH 
  orderbook{}(this.packageType, this.entries) as book 
SELECT askPriceForVolume(book, 1) as 'ask_price', bidPriceForVolume(book, 0.2) as 'bid_price'
FROM BINANCE
WHERE symbol == 'BTC/USD'

averageAskPriceForVolume() / averageBidPriceForVolume()​

Returns an average price required to execute a given volume on the ask/bid side of the order book. If there is less volume on the market than chosen, the function returns NaN. Returned price is calculated as weighted (by quantity) average: sum(price[i] * quantity[i]) / sum(quantity[i]). Note, that in the above-mentioned formula sum(quantity[i]) = volume (volume is provided by function argument), so the last level quantity will be truncated to satisfy the provided equation.

Note that in case of negative volume provided, the function not necessarily returns NaN. It returns the first level price if there is at least one level in the book.

WITH 
  orderbook{}(this.packageType, this.entries) as book 
SELECT averageAskPriceForVolume(book, 0.3) as 'avg_ask_price', averageAskPriceForVolume(book, 1) as 'avg_bid_price'
FROM BINANCE
WHERE symbol == 'BTC/USD'

askVolumeByPriceLevel() / bidVolumeByPriceLevel()​

Returns total volume which can be executed at the specified price on the ask/bid side of the order book. If there are no quotes on the required side of the book, it returns 0.

WITH 
  orderbook{}(this.packageType, this.entries) as book 
SELECT askVolumeByPriceLevel(book, 22600) as `ask_volume_by`, bidVolumeByPriceLevel(book, 22590) as `bid_volume_by`
FROM BINANCE
WHERE symbol == 'BTC/USD'

askVolumeAtPriceLevel() / bidVolumeAtPriceLevel()​

Returns the maximum level (level with the closest price to specified) volume which can be executed at the specified price on the ask/bid side of the order book. If there are no quotes on the required side of the book, it returns 0.

WITH 
  orderbook{}(this.packageType, this.entries) as book 
SELECT askVolumeAtPriceLevel(book, 22600) as `ask_volume_at`, bidVolumeAtPriceLevel(book, 22590) as `bid_volume_at`
FROM BINANCE
WHERE symbol == 'BTC/USD'

Example​

QuantityPrice
6103
4102
2101
SPREAD0.01
1100
399
598
askPriceForVolume(book, 0.1) -- 101
askPriceForVolume(book, 2.1) -- 102
askPriceForVolume(book, 100) -- NaN, not enough quantity on exchange book

bidPriceForVolume(book, 0.1) -- 100
bidPriceForVolume(book, 1.1) -- 99
bidPriceForVolume(book, 100) -- NaN, not enough quantity on exchange book

averageAskPriceForVolume(book, 0.1) -- 101
averageAskPriceForVolume(book, 4.0) -- 101.5, 2 by 101$ and 2 by 102$
averageAskPriceForVolume(book, 100) -- NaN, not enough quantity on exchange book

averageBidPriceForVolume(book, 0.1) -- 100
averageBidPriceForVolume(book, 2.0) -- 99.5, 1 by 100$ and 1 by 99$
averageBidPriceForVolume(book, 100) -- NaN, not enough quantity on exchange book

askVolumeByPriceLevel(book, 99)  -- 0
askVolumeByPriceLevel(book, 102) -- 6, 2 by 101$ and 4 by 102$

bidVolumeByPriceLevel(book, 101) -- 0
bidVolumeByPriceLevel(book, 99)  -- 4, 1 by 100$ and 3 by 99$

askVolumeAtPriceLevel(book, 100)   -- 0
askVolumeAtPriceLevel(book, 102.5) -- 4

bidVolumeAtPriceLevel(book, 101)  -- 0
bidVolumeAtPriceLevel(book, 98.5) -- 3

Histogram function​

info

The Histogram function is available starting with TimeBase 5.6.154.

The histogram function computes quantiles over the distribution of a numeric expression in your dataset, based on the HdrHistogram library. It supports both integer (long) and floating-point (double) values.

-- Calculate percentiles of latencies
SELECT histogram{q:[0.5, 0.75, 0.90, 0.99, 0.999, 0.9999, 0.99999]}(timestamp - originalTimestamp) as hist FROM BINANCE

Parameters

  • q: (array of numbers): Array of quantiles to compute (e.g., [0.5, 0.75, 0.99]). Each quantile should be between 0 and 1.

  • significantDigits: (integer, 0–5): Specifies the precision to use. This is the number of significant decimal digits to which the histogram will maintain value resolution and separation. Must be a non-negative integer between 0 and 5.

  • min: (integer ≥ 1): The lowest value that can be discerned (distinguished from 0) by the histogram. Must be a positive integer that is >= 1. May be internally rounded down to nearest power of 2.

  • max: (integer ≥ 2): The highest value to be tracked by the histogram. Must be a positive integer that is >= 2.

  • maxToMinRatio (double > 0, only for double histograms): specifies the dynamic range to use.

Result

  • Returns an array with the same length as the input q array, where each value is the computed quantile from the source data.